<?php

namespace app\advertisement\logic;

use app\advertisement\model\TikAccountData as TikAccountDataModel;
use think\facade\Db;
use app\data\util\HttpUtil;
use ba\Exception;
use think\facade\Cache;
use app\common\library\Excel;

class TikAccountDataLogic
{
    protected $model = null;

    public function __construct()
    {
        $this->model = new TikAccountDataModel();
    }

    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 列表
     * @author zhangheng
     */
    public function index($params = [])
    {
        $time = $params['time'] ?? '';
        $advertiser_name = $params['advertiser_name'] ?? '';
        $campaign_name = $params['campaign_name'] ?? '';
        $ad_name = $params['ad_name'] ?? '';
        $limit = $params['limit'] ?? 10;
        $where = [];
        //总消耗不为0
        // $where[] = ['a.spend', '>=', 0.001];

        if (!empty($time)) {
            $where[] = ['a.stat_time', '>=', strtotime($time[0])];
            $where[] = ['a.stat_time', '<=', strtotime($time[1])];
        }
        if (!empty($advertiser_name)) {
            $where[] = ['b.advertiser_name', 'like', '%' . $advertiser_name . '%'];
        }
        if (!empty($campaign_name)) {
            $where[] = ['a.campaign_name', 'like', '%' . $campaign_name . '%'];
        }
        if (!empty($ad_name)) {
            $where[] = ['a.ad_name', 'like', '%' . $ad_name . '%'];
        }

        $list = Db::name('advertising_campaign')
            ->field('a.*,b.advertiser_name')
            ->alias('a')
            ->join('ba_advaccount b', 'b.advertiser_id = a.advertiser_id', 'left')
            ->where($where)
            ->whereNotIn('b.id', function($query) {  
                $query->name('advaccount_admin')  
                    ->where('admin_id', '=', 37)  
                    ->field('advertiser_id');  
            })
            ->order('a.stat_time desc,a.spend desc')
            ->paginate($limit);
        $sum = Db::name('advertising_campaign')
            ->field('sum(a.spend) as spend,sum(a.total_onsite_shopping_value) as total_onsite_shopping_value,sum(a.conversion) as conversion,AVG(a.onsite_shopping_roas) as onsite_shopping_roas')
            ->alias('a')
            ->join('ba_advaccount b', 'b.advertiser_id = a.advertiser_id', 'left')
            ->where($where)
            ->whereNotIn('b.id', function($query) {  
                $query->name('advaccount_admin')  
                    ->where('admin_id', '=', 37)  
                    ->field('advertiser_id');  
            })
            ->find();
            
        $lists = $list->items();

        $data = [
            'list' => $lists,
            'total' => $list->total(),
            'spend' => number_format($sum['spend'] ?? 0, 2, '.', ''),
            'total_onsite_shopping_value' => number_format($sum['total_onsite_shopping_value'] ?? 0, 2, '.', ''),  
            'conversion' => number_format($sum['conversion'] ?? 0, 2, '.', ''),  
            'onsite_shopping_roas' => $sum['spend'] ? number_format($sum['total_onsite_shopping_value'] / $sum['spend'], 2, '.', '') : 0,
        ];
        return json(DefaultResponse(200, true, '获取成功', $data));
    }

    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 导出
     * @author zhangheng
     */
    public function export($params = [])
    {
        $time = $params['time'] ?? '';
        $advertiser_name = $params['advertiser_name'] ?? '';
        $campaign_name = $params['campaign_name'] ?? '';
        $ad_name = $params['ad_name'] ?? '';
        $where = [];
        //总消耗不为0
        // $where[] = ['a.spend', '>=', 0.001];

        if (!empty($time)) {
            $where[] = ['a.stat_time', '>=', strtotime($time[0])];
            $where[] = ['a.stat_time', '<=', strtotime($time[1])];
        }
        if (!empty($advertiser_name)) {
            $where[] = ['b.advertiser_name', 'like', '%' . $advertiser_name . '%'];
        }
        if (!empty($campaign_name)) {
            $where[] = ['a.campaign_name', 'like', '%' . $campaign_name . '%'];
        }
        if (!empty($ad_name)) {
            $where[] = ['a.ad_name', 'like', '%' . $ad_name . '%'];
        }
        if(empty($where)){
            $limit = 10;
        }else{
            $limit = 100000;
        }

        $list = Db::name('advertising_campaign')
            ->field('a.*,b.advertiser_name')
            ->alias('a')
            ->join('ba_advaccount b', 'b.advertiser_id = a.advertiser_id', 'left')
            ->where($where)
            ->limit($limit)
            ->order('a.stat_time desc,a.spend desc')
            ->select()
            ->toarray();
        
        $header[] = ['广告账户', 'advertiser_id'];
        $header[] = ['推广系列', 'campaign_name'];
        $header[] = ['广告名称', 'ad_name'];
        $header[] = ['日期', 'stat_time_day'];
        $header[] = ['总消耗', 'spend'];
        $header[] = ['总收入(商店)', 'total_onsite_shopping_value'];
        $header[] = ['付费数(商店)', 'conversion'];
        $header[] = ['ROAS(商店)', 'onsite_shopping_roas'];
        $fileName = "export_advaccount" . time();
        return Excel::exportData($list, $header, $fileName, 'xlsx');
    }

}
